package comun;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import oracle.jdbc.OracleTypes;

import planet.bean.BeanEmpleo;

import planet.comun.OracleDBConn;

public class EmpleoImp implements Empleo {
	///coneccion bd Oracle////
	private Connection getConnection() {
		OracleDBConn cnx = new OracleDBConn();
		return cnx.getConnection();
	}
	///coneccion bd Oracle////

	@Override
	public Collection listarEmpleo() throws Exception {
		Collection objLista = new ArrayList();
		BeanEmpleo bean = null;
	    try {
	      Connection conn = getConnection();
	      boolean useOracleQuery = true;
	      String oracleQuery = "begin ? := SF_GET_EMPLEO(?,?,?,?,?); end;";
	      String genericQuery = "{ call ? := SF_GET_EMPLEO(?,?,?,?,?) }";
	      String query = useOracleQuery ? oracleQuery : genericQuery;
	      CallableStatement stmt = conn.prepareCall(query);
	      stmt.registerOutParameter(1, OracleTypes.CURSOR);
	      stmt.setString(2,"A");
	      stmt.setString(3," ");
	      stmt.setString(4," ");
	      stmt.setString(5," ");
	      stmt.setString(6," ");
	      stmt.execute();
	      ResultSet rs = (ResultSet)stmt.getObject(1);
	      while (rs.next()) {
	    	  bean = new BeanEmpleo();
	    	  bean.setCOD_EMPLEO(rs.getString(1));
	    	  bean.setNOM_EMPLEO(rs.getString(2));
	    	  objLista.add(bean);
	      }
	      rs.close();
	      conn.close();
	    }catch (SQLException e) {
	      e.printStackTrace();
	    }
	    return objLista;
	}

	@Override
	public void insertar_Empleo(BeanEmpleo entidad) throws Exception {
		System.out.println("LLegoo----oracle");
		 Connection conn = getConnection();
		    CallableStatement cs=null;
		    cs= conn.prepareCall("{call SP_INS_EMPLEO(?)}");
		    try{
		      conn = getConnection();
		      cs.setString(1,entidad.getNOM_EMPLEO());
		      cs.executeUpdate ();
		    }catch(SQLException e){
		      System.out.println(e.toString());
		    }finally{
		      cs.close();
		      conn.close();
		    }

	}
		

	@Override
	public void actualizar_Empleo(BeanEmpleo entidad) throws Exception {
		Connection conn = getConnection();
	    CallableStatement cs=null;
	    cs= conn.prepareCall("{call SP_UPD_EMPLEO(?,?)}");
	    try{
	      conn = getConnection();
	      cs.setString(1,entidad.getCOD_EMPLEO());
	      cs.setString(2,entidad.getNOM_EMPLEO());
	      cs.executeUpdate ();
	    }catch(SQLException e){
	      System.out.println(e.toString());
	    }finally{
	      cs.close();
	      conn.close();
	    }
	}
	
	

}
